package com.xff.dao;

import com.xff.bean.ApplyBean;
import com.xff.bean.BookBean;
import com.xff.bean.SubscribeBean;
import com.xff.dbUtils.DbUtil;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;


public class ApplyDao {

    /*
	* 添加申请借阅的记录
	*
	* */
    public void addApplyBook(ApplyBean applyBean) {
        //连接数据库
        Connection conn = DbUtil.getConn();
        //SQL
        String sql = "insert into apply(aid,bid,card,bookname,username,name,applytime,status) values(?,?,?,?,?,?,?,?)";
        int rs = 0;
        PreparedStatement stm = null;
        try {
            //预编译SQL，减少sql执行
            stm = conn.prepareStatement(sql);
            //添加
            stm.setInt(1,applyBean.getAid());
            stm.setInt(2,applyBean.getBid());
            stm.setString(3, applyBean.getCard());
            stm.setString(4,applyBean.getBookname());
            stm.setString(5,applyBean.getUsername());
            stm.setString(6,applyBean.getName());

            SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String date=format.format(new java.util.Date());
            stm.setString(7,date);
            stm.setInt(8,0);

            //执行更新
            rs = stm.executeUpdate();

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

    /*
   * 申请借阅查询
   * */
    public ArrayList<ApplyBean> get_ApplyListInfo(){
        ArrayList<ApplyBean> tag_Array = new ArrayList<ApplyBean>();
        Connection conn = DbUtil.getConn();
        String sql = "select * from apply where status=0 ";
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();
            while(rs.next()){
                ApplyBean tag = new ApplyBean();
                tag.setApplyid(rs.getInt("applyid"));
                tag.setAid(rs.getInt("aid"));
                tag.setBid(rs.getInt("bid"));
                tag.setBookname(rs.getString("bookname"));
                tag.setCard(rs.getString("card"));
                tag.setUsername(rs.getString("username"));
                tag.setName(rs.getString("name"));
                tag.setApplytime(rs.getDate("applytime"));
                tag.setStatus(rs.getInt("status"));
                tag_Array.add(tag);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return tag_Array;
    }

    /**
     * 获取借阅记录，传入的条件有name，tip，表示搜索相关图书名称和读者账号/姓名的记录
     *
     */
    public ArrayList<ApplyBean> get_ApplyListInfo(int tip, String name,int rank){
        ArrayList<ApplyBean> tag_Array = new ArrayList<ApplyBean>();
        Connection conn = DbUtil.getConn();
        String sql="select * from apply";
        if(tip==1&&rank==1)//根据书名查询
            sql = "select * from apply where status=0 AND bookname like '%"+ name +"%' ORDER BY applytime DESC ";
        if(tip==1&&rank==2)//根据书名查询
            sql = "select * from apply where status=0 AND bookname like '%"+ name +"%' ORDER BY applytime ";

        if(tip==2)//根据用户查询
            sql = "select * from apply where status=0 AND (name like '%"+ name +"%' or username like '%"+name+"%')";
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                ApplyBean tag = new ApplyBean();
                tag.setApplyid(rs.getInt("applyid"));
                tag.setAid(rs.getInt("aid"));
                tag.setBid(rs.getInt("bid"));
                tag.setBookname(rs.getString("bookname"));
                tag.setCard(rs.getString("card"));
                tag.setUsername(rs.getString("username"));
                tag.setName(rs.getString("name"));
                tag.setApplytime(rs.getDate("applytime"));
                tag.setStatus(rs.getInt("status"));
                tag_Array.add(tag);

            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return tag_Array;
    }

    /*
    * 根据用户名查询
    * */
    public ArrayList<ApplyBean> get_ApplyListInfo(String username){
        ArrayList<ApplyBean> tag_Array = new ArrayList<ApplyBean>();
        Connection conn = DbUtil.getConn();
        String sql="select * from apply where username='"+username+"'";
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                ApplyBean tag = new ApplyBean();
                tag.setApplyid(rs.getInt("applyid"));
                tag.setAid(rs.getInt("aid"));
                tag.setBid(rs.getInt("bid"));
                tag.setBookname(rs.getString("bookname"));
                tag.setCard(rs.getString("card"));
                tag.setUsername(rs.getString("username"));
                tag.setName(rs.getString("name"));
                tag.setApplytime(rs.getDate("applytime"));
                tag.setStatus(rs.getInt("status"));
                tag_Array.add(tag);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return tag_Array;
    }

    /*
    * 根据用户名、bid、状态查询
    * */
    public ArrayList<ApplyBean> get_ApplyListInfo(String username,int bid,int status){
        ArrayList<ApplyBean> tag_Array = new ArrayList<ApplyBean>();
        Connection conn = DbUtil.getConn();
        String sql="select * from apply where username=? AND bid=? AND status=?";
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.setString(1,username);
            stm.setInt(2,bid);
            stm.setInt(3,status);
            rs = stm.executeQuery();
            while (rs.next()) {
                ApplyBean tag = new ApplyBean();
                tag.setApplyid(rs.getInt("applyid"));
                tag.setAid(rs.getInt("aid"));
                tag.setBid(rs.getInt("bid"));
                tag.setBookname(rs.getString("bookname"));
                tag.setCard(rs.getString("card"));
                tag.setUsername(rs.getString("username"));
                tag.setName(rs.getString("name"));
                tag.setApplytime(rs.getDate("applytime"));
                tag.setStatus(rs.getInt("status"));
                tag_Array.add(tag);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return tag_Array;
    }

    public void updateApply_status(int applyid,int status) {
        // TODO Auto-generated method stub
        Connection conn = DbUtil.getConn();
        String sql = "update apply set status=? where applyid=?";
        PreparedStatement stm = null;
        try {
            stm = conn.prepareStatement(sql);

            stm.setInt(1,status);
            stm.setInt(2,applyid);

            stm.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void updateApply_allstatus(int bid,int status){
        Connection conn = DbUtil.getConn();
        String sql = "update apply set status=? where bid=? AND status=?";
        PreparedStatement stm = null;
        try {
            stm = conn.prepareStatement(sql);

            stm.setInt(1,status);
            stm.setInt(2,bid);
            stm.setInt(3,0);
            stm.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void updateApply_userstatus(int aid,int status){
        Connection conn = DbUtil.getConn();
        String sql = "update apply set status=? where aid=? AND status=?";
        PreparedStatement stm = null;
        try {
            stm = conn.prepareStatement(sql);

            stm.setInt(1,status);
            stm.setInt(2,aid);
            stm.setInt(3,0);
            stm.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public void deleteApply(int applyid) {
        Connection conn = DbUtil.getConn();
        String sql = "delete from apply where applyid=?";
        PreparedStatement stm = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.setInt(1, applyid);
            stm.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }


}
